<?php
/**
 * Created by PhpStorm.
 * User: chunyang
 * Date: 2018/4/12
 * Time: 11:45
 */

namespace Biz\service\settlement;

use Biz\model\admin\AdminUser as mShopAdminUser;
use Biz\model\GoodsBasicInfo;
use Biz\model\GoodsBasicInfo as mGoodsBasicInfo;
use Biz\model\GoodsSku;
use Biz\model\settlement\BaseAccountTransaction;
use Biz\model\settlement\BillOperateLog as mBillOperateLog;
use Biz\model\settlement\Bills as mBills;
use Biz\model\settlement\HhzCost as mHhzConst;
use Biz\model\settlement\Settlement as mSettlement;
use Biz\model\settlement\SettlementAccount as mSettlementAccount;
use Biz\model\settlement\WillSettlement as mWillSettlement;
use Biz\model\ShopBankInfo as mShopBankInfo;
use Biz\model\ShopBasicInfo as mShopBasicInfo;
use Biz\model\ShopCompanyInfo as mShopCompanyInfo;
use Biz\model\ShopInvoiceInformation as mShopInvoiceInformation;
use Biz\model\ShopPersonalInfo as mShopPersonalInfo;
use Biz\model\ShopSecurityDeposit as mShopSecurityDeposit;
use Biz\model\theParagraph\Store as mStore;
use Biz\model\trade\CustomizePayWay as mCustomizePayWay;
use Biz\model\trade\Order as mOrder;
use Biz\model\trade\OrderExtend as msOrderExtend;
use Biz\model\trade\OrderGoods as mOrderGoods;
use Biz\model\trade\OrderPackage as mOrderPackage;
use Biz\model\trade\OrderRefund as mOrderRefund;
use Biz\pockets\constInterface\ActivityInfo;
use Biz\pockets\constInterface\PayConfig;
use Biz\pockets\excel\DownExcel;
use Biz\tools\Mail;
use Biz\tools\ShopArea;
use Biz\model\brand\Brand as mBrand;
use Doraemon\model\Category as mCategory;
use Doraemon\service\User;
/**
 * @deprecated
 */
class Settlement
{
    /**
     * 获取店铺账户信息
     * @param $shopId
     * @return array
     */
    public static function mgtGetAccountInfo($shopId)
    {
        $result = [];
        $accountInfo = mSettlementAccount::getAccountInfo($shopId);
        if ($accountInfo) {
            $result = $accountInfo;
        } else {
            $result['balance'] = 0;
            $result['withdrawal_amount'] = 0;
        }
        $depostitList = mShopSecurityDeposit::getDepositList($shopId);
        $depostitInfo = array_shift($depostitList);
        if ($depostitInfo) {
            $result['deposit_balance'] = $depostitInfo['balance'];
            $result['deposit_debts'] = ($depostitInfo['balance'] < $depostitInfo['amount']) ? bcsub($depostitInfo['amount'], $depostitInfo['balance'], 2) : 0;
        } else {
            $result['deposit_balance'] = 0;
            $result['deposit_debts'] = 0;
        }
        return $result;
    }

    /**
     * 获取交易明细
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @param int $page
     * @param int $pageSize
     */
    public static function mgtGetTransactionList($shopId, $startTime, $endTime, $orderNo, $page = 1, $pageSize = 20)
    {
        $limit = [($page - 1) * $pageSize, $pageSize];
        $list = mSettlement::getTransactionList($shopId, $startTime, $endTime, $orderNo, $limit);
        return $list;
    }

    /**
     * 获取交易明细总数
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @return mixed
     */
    public static function mgtGetTransactionCount($shopId, $startTime, $endTime, $orderNo)
    {
        return mSettlement::getTransactionCount($shopId, $startTime, $endTime, $orderNo);
    }

    /**
     * 交易明细下载
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @return bool
     */
    public static function mgtDownTransactionList($shopId, $startTime, $endTime, $orderNo)
    {
        $list = mSettlement::getTransactionList($shopId, $startTime, $endTime, $orderNo);
        if (!$list) {
            return false;
        }
        $data = [];
        foreach ($list as $rows) {
            $data[] = [
                'id' => $rows['id'],
                'operate_time' => $rows['operate_time'],
                'transaction_type_desc' => $rows['transaction_type_desc'],
                'transaction_amount' => $rows['transaction_amount'],
                'order_no' => $rows['order_no'],
                'remark' => $rows['remark'],
            ];
        }

        $header = ['交易编号', '交易时间', '交易类型', '交易金额', '订单编号', '备注'];
        $letter = range('A', 'F');
        $excelObj = new DownExcel();
        $excelObj->setFileName('交易明细' . time());
        $excelObj->setSheetTitle('交易明细');
        //设置列宽
        $widths = ['A' => 18, 'B' => 30, 'C' => 20, 'D' => 40, 'E' => 45, 'F' => 60];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        $excelObj->create($data, $header);
    }


    /**
     * 商家后台查询账单期内已确认收货订单
     * @param int $shopId 店铺id
     * @param string $billDate 账单日期
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function mgtGetConfirmOrderList($shopId, $billDate, $page = 1, $pageSize = 20)
    {
        $startTime = $billDate;
        $endTime = date('Y-m-d H:i:s', strtotime("+1 month -1 second", strtotime($billDate)));
        $list = self::getConfirmOrderList($shopId, $startTime, $endTime, $page, $pageSize);
        return $list;
    }

    public static function mgtGetConfirmOrderCount($shopId, $billDate)
    {
        $startTime = $billDate;
        $endTime = date('Y-m-d H:i:s', strtotime("+1 month -1 second", strtotime($billDate)));
        return self::getConfirmOrderCount($shopId, $startTime, $endTime);
    }

    /**
     * 商家后台导出已确认收货订单
     * @param $shopId 店铺id
     * @param $billDate 账单日期
     * @return array
     */
    public static function mgtDownConfirmOrderList($shopId, $billDate)
    {
        $startTime = $billDate;
        $endTime = date('Y-m-d H:i:s', strtotime("+1 month -1 second", strtotime($billDate)));
        $list = self::getConfirmOrderList($shopId, $startTime, $endTime, 0, 0);
        $dataArr = [];
        $skuIds = [];
        foreach ($list as $orderNo => $orderInfo) {
            $i = 0;
            foreach ($orderInfo['order_goods'] as $orderGoodsInfo) {
                if ($i == 0) {
                    $rows = [
                        'order_no' => $orderNo,
                        'shipping_amount' => $orderInfo['shipping_amount'],
                        'goods_amount' => $orderInfo['goods_amount'],
                        'refund_shipping_amount' => $orderInfo['refund_shipping_amount'],
                        'refund_amount' => $orderInfo['refund_amount'],
                    ];
                } else {
                    $rows = [
                        'order_no' => $orderNo,
                        'shipping_amount' => 0,
                        'goods_amount' => 0,
                        'refund_shipping_amount' => 0,
                        'refund_amount' => 0,
                    ];
                }
                $rows['goods_id'] = $orderGoodsInfo['goods_id'];
                $rows['goods_name'] = $orderGoodsInfo['goods_title'];
                $rows['sku_desc'] = '';
                $rows['sku_no'] = '';
                $rows['sell_price'] = $orderGoodsInfo['sell_price'];
                $rows['buy_price'] = $orderGoodsInfo['buy_price'];
                $rows['buy_num'] = $orderGoodsInfo['buy_num'];
                $rows['shop_discount_amount'] = 0 - abs($orderGoodsInfo['shop_discount_amount']);
                $rows['activity_allowance'] = $orderGoodsInfo['activity_allowance'];
                $rows['goods_refund_amount'] = $orderGoodsInfo['refund_amount'];
                $rows['commission_rate'] = $orderGoodsInfo['commission_rate'];
                $rows['commissions'] = $orderGoodsInfo['commissions'];
                $rows['settlement_amount'] = ($orderGoodsInfo['buy_price'] * $orderGoodsInfo['buy_num']) - abs($orderGoodsInfo['refund_amount']) - abs($orderGoodsInfo['commissions']) - abs($orderGoodsInfo['shop_discount_amount']) + abs($orderGoodsInfo['activity_allowance']);
                $rows['promo_type'] = ($orderGoodsInfo['promo_type'] && $orderGoodsInfo['promo_type'] == ActivityInfo::ACTIVITY_TYPE_LIMIT_TIME_ROB) ? '限时购' : '';
                $rows['addtime'] = $orderGoodsInfo['addtime'];
                $dataArr[$orderNo][$orderGoodsInfo['sku_id']] = $rows;
                array_push($skuIds, $orderGoodsInfo['sku_id']);
                $i++;
            }
        };
        $skuList = GoodsSku::getInfoList($skuIds, true, false);
        $data = [];
        foreach ($dataArr as $orderNo => $orderList) {
            foreach ($orderList as $skuId => $skuInfo) {
                if (isset($skuList[$skuId])) {
                    $skuInfo['sku_desc'] = implode(' ', $skuList[$skuId]['sku_attr_desc']);
                    $skuInfo['sku_no'] = $skuList[$skuId]['sku_no'];
                }
                $data[] = $skuInfo;
            }
        }
        $header = ['订单号', '订单运费', '商品总金额', '退运费', '订单累计退款金额', '商品编号', '商品名称', '商品SKU', 'SKU商家编码', '商品原价', '商品成交价', '商品数量', '店铺优惠', '平台活动补贴', '商品累计退款金额', '佣金比例%', '佣金金额', '商品结算金额', '参加活动', '下单时间'];
        $letter = range('A', 'T');
        $excelObj = new DownExcel();
        $excelObj->setFileName('结算对账导出' . $billDate);
        $excelObj->setSheetTitle('结算对账导出');
        //设置列宽
        $widths = ['A' => 35, 'B' => 25, 'C' => 25, 'D' => 25, 'E' => 25, 'F' => 20, 'G' => 35, 'H' => 35, 'I' => 30, 'J' => 25, 'K' => 25, 'L' => 20, 'M' => 25, 'N' => 25, 'O' => 25, 'P' => 25, 'Q' => 25, 'R' => 25, 'S' => 25, 'T' => 25];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        $excelObj->create(array_values($data), $header);
        return $list;
    }

    /**
     * 查询账单期内退款订单列表
     * @param $shopId
     * @param string $billDate 账单日期
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function mgtGetRefundOrderList($shopId, $billDate, $page = 1, $pageSize = 20)
    {
        $startTime = $billDate;
        $endTime = date('Y-m-d H:i:s', strtotime("+1 month -1 second", strtotime($billDate)));
        $result = self::getRefundOrderList($shopId, $startTime, $endTime, $page, $pageSize);
        return $result;
    }

    public static function mgetGetOtherTransList($shopId, $billDate, $page = 1, $pageSize = 20)
    {
        $startTime = $billDate;
        $endTime = date('Y-m-d H:i:s', strtotime("+1 month -1 second", strtotime($billDate)));
        $limit = [($page - 1) * $pageSize, $pageSize];
        return mSettlement::getOtherTransList($shopId, $startTime, $endTime, $limit);
    }

    public static function mgtGetUnConfirmedBills($shopId)
    {
        $where = [
            'shop_id' => $shopId,
            'channel' => mBills::CHANNEL_ALL,
            'status' => mBills::STATUS_NORMAL
        ];
        return mBills::getBillList('*', $where, [], ['bill_date' => 'DESC']);
    }

    /**
     * 查询账单期内已确认收货订单
     * @param int $shopId 店铺id
     * @param $startTime
     * @param $endTime
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function getConfirmOrderList($shopId, $startTime, $endTime, $page = 1, $pageSize = 20)
    {
        if ($page && $pageSize) {
            $limit = [($page - 1) * $pageSize, $pageSize];
        } else {
            $limit = [];
        }
        $orderNos = mSettlement::getConfirmOrderNos($shopId, $startTime, $endTime, $limit);
        if (!$orderNos) {
            return [];
        }
        $result = mSettlement::getConfirmOrderList($orderNos, $startTime, $endTime);
        $orderTransList = [];
        foreach ($result as $row) {
            $orderTransList[$row['order_no']][$row['transaction_type']][] = $row;
        }
        $transOrderNos = array_keys($orderTransList);

        $willResult = mWillSettlement::getList($transOrderNos, null, null);
        $willTransList = [];
        foreach ($willResult as $row) {
            $willTransList[$row['order_no']][$row['transaction_type']][] = $row;
        }

        $orders = mOrder::getOrderInfoList(['order_no' => $transOrderNos], [], [], false);
        $orderList = [];
        foreach ($orders as $row) {
            $orderList[$row['order_no']] = $row;
        }

        $orderGoods = mOrderGoods::getOrderGoodsList($transOrderNos, [], null);
        $orderGoodsList = [];
        foreach ($orderGoods as $row) {
            $row['spec_img_url'] = $row['cover_img_url'];
            $row['spec_img_thumb_url'] = $row['cover_img_thumb_url'];
            $orderGoodsList[$row['order_no']][] = $row;
        }
        $list = self::_formatConfirmOrderList($orderTransList, $willTransList, $orderList, $orderGoodsList);

        return $list;
    }

    public static function getConfirmOrderCount($shopId, $startTime, $endTime)
    {
        return mSettlement::getConfirmOrderCount($shopId, $startTime, $endTime);
    }

    private static function _formatConfirmOrderList($orderTransList, $willTransList, $orderList, $orderGoodsList)
    {
        $result = [];
        foreach ($orderTransList as $orderNo => $transList) {
            $result[$orderNo]['order_no'] = $orderNo;
            $result[$orderNo]['operate_time'] = $transList[mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_RMB][0]['operate_time'];
            $result[$orderNo]['goods_amount'] = $orderList[$orderNo]['goods_amount'];
            $result[$orderNo]['shop_discount_amount'] = bcadd($orderList[$orderNo]['s_coupon_amount'], $orderList[$orderNo]['meal_amount'], 2);//店铺累计优惠
            $result[$orderNo]['shipping_amount'] = $orderList[$orderNo]['shipping_amount'];
            $result[$orderNo]['activity_allowance'] = $orderList[$orderNo]['activity_allowance'];
            $result[$orderNo]['discount_amount'] = $orderList[$orderNo]['integral_amount'] + $orderList[$orderNo]['coupon_amount'] + $orderList[$orderNo]['packet_amount'];
            $result[$orderNo]['pay_amount'] = $orderList[$orderNo]['pay_amount'];
            $refundAmount = 0.00;//累计退款金额
            $refundShippingAmount = 0.00;//
            $statementAmount = 0.00;//结算金额
            $allCommissions = 0.00;//佣金
            $skuCommissionsArr = [];
            $refundSkuAmountArr = [];
            foreach ($transList as $transType => $typeList) {
                foreach ($typeList as $row) {
                    if (!isset($refundSkuAmountArr[$row['sku_id']])) {
                        $refundSkuAmountArr[$row['sku_id']] = 0;
                    }
                    if (!isset($skuCommissionsArr[$row['sku_id']])) {
                        $skuCommissionsArr[$row['sku_id']] = 0;
                    }
                    $statementAmount = bcadd($statementAmount, $row['transaction_amount'], 2);
                    if ($transType == mSettlement::TRANSACTION_TYPE_DEDUCT_COMMISSION) {
                        $allCommissions = bcadd($allCommissions, $row['transaction_amount'], 2);
                        $skuCommissionsArr[$row['sku_id']] = bcadd($skuCommissionsArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_BACK_COMMISSION) {
                        $allCommissions = bcadd($allCommissions, $row['transaction_amount'], 2);
                        $skuCommissionsArr[$row['sku_id']] = bcadd($skuCommissionsArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_ORDER_REFUND) {
                        $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                        $refundShippingAmount = bcadd($refundShippingAmount, $row['shipping_amount'], 2);
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                        //累计退款，需要减去退运费金额
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['shipping_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_BACK_INTEGRAL) {
                        $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_HHZ_BACK_COUPON) {
                        $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE) {
                        $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                    if ($transType == mSettlement::TRANSACTION_TYPE_BACK_RED_PACKET) {
                        $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                        $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                    }
                }
            }
            if (isset($willTransList[$orderNo])) {
                foreach ($willTransList[$orderNo] as $transType => $typeList) {
                    foreach ($typeList as $row) {
                        if (!isset($refundSkuAmountArr[$row['sku_id']])) {
                            $refundSkuAmountArr[$row['sku_id']] = 0;
                        }
                        if ($transType == mSettlement::TRANSACTION_TYPE_ORDER_REFUND) {
                            $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                            $refundShippingAmount = bcadd($refundShippingAmount, $row['shipping_amount'], 2);
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                            //累计退款，需要减去退运费金额
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['shipping_amount'], 2);
                        }
                        if ($transType == mSettlement::TRANSACTION_TYPE_BACK_INTEGRAL) {
                            $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                        }
                        if ($transType == mSettlement::TRANSACTION_TYPE_HHZ_BACK_COUPON) {
                            $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                        }
                        if ($transType == mSettlement::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE) {
                            $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                        }
                        if ($transType == mSettlement::TRANSACTION_TYPE_BACK_RED_PACKET) {
                            $refundAmount = bcadd($refundAmount, $row['transaction_amount'], 2);
                            $refundSkuAmountArr[$row['sku_id']] = bcadd($refundSkuAmountArr[$row['sku_id']], $row['transaction_amount'], 2);
                        }
                    }
                }
            }
            $result[$orderNo]['refund_amount'] = $refundAmount;
            $result[$orderNo]['refund_shipping_amount'] = $refundShippingAmount;
            $result[$orderNo]['commissions'] = abs(self::_numberFormat($allCommissions));
            $result[$orderNo]['settlement_amount'] = $statementAmount;

            $result[$orderNo]['order_goods'] = [];
            foreach ($orderGoodsList[$orderNo] as $row) {
                $refundCommissionSkuIds = array_keys($skuCommissionsArr);
                if (in_array($row['sku_id'], $refundCommissionSkuIds)) {//如果退款发生在账单月份内
                    $commissions = $row['buy_price'] * $row['buy_num'] + abs($row['activity_allowance']) - abs($row['s_coupon_amount']) - abs($row['meal_amount']);
                    $commissions = ($commissions * $row['commission_rate']) / 100;
                    $commissions = round($commissions, 2);
                    if (isset($skuCommissionsArr[$row['sku_id']])) {
                        $commissions = bcsub($commissions, abs($skuCommissionsArr[$row['sku_id']]), 2);
                    }
                } else {//账单未发生在账单内
                    $commissions = $row['buy_price'] * $row['buy_num'] + abs($row['activity_allowance']) - abs($row['s_coupon_amount']) - abs($row['meal_amount']) - abs(isset($refundSkuAmountArr[$row['sku_id']]) ? $refundSkuAmountArr[$row['sku_id']] : 0);
                    $commissions = ($commissions * $row['commission_rate']) / 100;
                    $commissions = round($commissions, 2);
                }

                $result[$orderNo]['order_goods'][] = [
                    'sku_id' => $row['sku_id'],
                    'sku_desc' => $row['sku_desc'],
                    'goods_id' => $row['goods_id'],
                    'goods_name' => $row['title'] . ' ' . $row['sku_desc'],
                    'goods_title' => $row['title'],
                    'spec_img_url' => $row['spec_img_url'],
                    'spec_img_thumb_url' => $row['spec_img_thumb_url'],
                    'sell_price' => $row['sell_price'],
                    'buy_price' => $row['buy_price'],
                    'buy_num' => $row['buy_num'],
                    'promo_type' => $row['promo_type'],
                    'shop_discount_amount' => bcadd($row['s_coupon_amount'], $row['meal_amount'], 2),//店铺累计优惠
                    'activity_allowance' => $row['activity_allowance'],//平台活动补贴
                    'refund_amount' => isset($refundSkuAmountArr[$row['sku_id']]) ? $refundSkuAmountArr[$row['sku_id']] : 0.00,
                    'commission_rate' => $row['commission_rate'],
                    'commissions' => $commissions,
                    'addtime' => $row['addtime']
                ];
            }
        }
        return $result;
    }

    private static function _numberFormat($number)
    {
        return number_format($number, 2, '.', '');
    }

    /**
     * 查询账单期内退款订单列表
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function getRefundOrderList($shopId, $startTime, $endTime, $page = 1, $pageSize = 20)
    {
        $limit = [($page - 1) * $pageSize, $pageSize];
        $transTypes = [
            mSettlement::TRANSACTION_TYPE_ORDER_REFUND,
            mSettlement::TRANSACTION_TYPE_BACK_INTEGRAL,
            mSettlement::TRANSACTION_TYPE_HHZ_BACK_COUPON,
            mSettlement::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE,
            mSettlement::TRANSACTION_TYPE_BACK_RED_PACKET,
            mSettlement::TRANSACTION_TYPE_BACK_COMMISSION
        ];
        $whereRefund = [
            'operate_time[>=]' => $startTime,
            'operate_time[<=]' => $endTime,
            'transaction_type' => $transTypes,
        ];
        if ($shopId) {
            $whereRefund['shop_id'] = $shopId;
        }
        $refundOrderNos = mSettlement::getTransList('order_no', $whereRefund);
        $where = [
            'order_no' => $refundOrderNos,
            'transaction_type' => mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_RMB,
            'operate_time[<]' => $startTime,
        ];
        $confirmOrderNos = mSettlement::getTransList('order_no', $where, []);
        $whereRefund['order_no'] = $confirmOrderNos;
        $allCount = mSettlement::getTransListCount($whereRefund);
        $refundTransList = mSettlement::getTransList('*', $whereRefund, $limit);
        //查询退款信息
        $orderRefundList = mOrderRefund::getRefundCompleteListByOrderNoList($refundOrderNos);
        $list = self::_formatRefundOrderList($refundTransList, $orderRefundList);
        return ['list' => $list, 'count' => $allCount];
    }

    private static function _formatRefundOrderList($refundTransList, $orderRefundList)
    {
        $result = [];
        if ($refundTransList && $orderRefundList) {
            foreach ($refundTransList as $row) {
                $skuId = $row['sku_id'];
                $refundInfo = $orderRefundList[$row['order_no']][$row['sku_id']];
                if (!isset($result[$skuId])) {
                    $result[$skuId] = [
                        'operate_time' => $row['operate_time'],
                        'order_no' => $row['order_no'],
                        'type_refund_desc' => $refundInfo['type_refund_desc'],//退款类型
                        'refund_order_no' => $refundInfo['refund_order_no'],//退款编号
                        'goods_name' => $refundInfo['title'] . $refundInfo['sku_desc'],//商品名称
                        'reason_desc' => $refundInfo['reason_desc'],//退款原因
                        'transaction_amount' => 0,//退款金额
                        'commissions' => 0,//退款返佣金
                        'refund_allowance' => 0,//扣平台补贴
                        'settlement_amount' => 0//结算金额=退款金额+扣平台补贴-返还佣金
                    ];
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_ORDER_REFUND) {
                    $result[$skuId]['transaction_amount'] = abs($row['transaction_amount']);
                    $result[$skuId]['settlement_amount'] = bcadd($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_BACK_COMMISSION) {
                    $result[$skuId]['commissions'] = abs($row['transaction_amount']);
                    $result[$skuId]['settlement_amount'] = bcsub($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_BACK_INTEGRAL) {
                    $result[$skuId]['refund_allowance'] = bcadd($result[$skuId]['refund_allowance'], abs($row['transaction_amount']), 2);
                    $result[$skuId]['settlement_amount'] = bcadd($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_HHZ_BACK_COUPON) {
                    $result[$skuId]['refund_allowance'] = bcadd($result[$skuId]['refund_allowance'], abs($row['transaction_amount']), 2);
                    $result[$skuId]['settlement_amount'] = bcadd($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE) {
                    $result[$skuId]['refund_allowance'] = bcadd($result[$skuId]['refund_allowance'], abs($row['transaction_amount']), 2);
                    $result[$skuId]['settlement_amount'] = bcadd($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_BACK_RED_PACKET) {
                    $result[$skuId]['refund_allowance'] = bcadd($result[$skuId]['refund_allowance'], abs($row['transaction_amount']), 2);
                    $result[$skuId]['settlement_amount'] = bcadd($result[$skuId]['settlement_amount'], abs($row['transaction_amount']), 2);
                }
            }
        }
        return array_values($result);
    }

    /**
     * 获取店铺账单列表
     * @param $shopId
     * @return mixed
     */
    public static function mgetGetShopBillList($shopId)
    {
        return mBills::getBillList(['shop_id', 'bill_date', 'settlement_amount', 'status'], ['shop_id' => $shopId, 'channel' => mBills::CHANNEL_ALL], [], ['bill_date' => 'DESC']);
    }

    /**
     * 获取店铺账单总数
     * @param $shopId
     * @return mixed
     */
    public static function mgetGetShopBillListCount($shopId)
    {
        $where['shop_id'] = $shopId;
        $where['channel'] = mBills::CHANNEL_ALL;
        return mBills::getBillListCount($where);
    }

    /**
     * 店铺确认账单
     * @param $shopId
     * @param $billDate
     * @return mixed
     */
    public static function mgetChangeBillStatus($shopId, $billDate)
    {
        return mBills::updateData(['status' => mBills::STATUS_WILL_TRANSFER, 'confirm_bill_time' => date('Y-m-d H:i:s')], ['shop_id' => $shopId, 'bill_date' => $billDate]);
    }

    /**
     * 获取当月结算金额
     * @param $shopId
     * @param $billDate
     */
    public static function mgetGetBillBalance($shopId, $billDate)
    {
        $result = mBills::getBillList('settlement_amount', ['shop_id' => $shopId, 'bill_date' => $billDate, 'channel' => mBills::CHANNEL_ALL]);
        return $result ? array_shift($result) : 0;
    }


    /**
     * 生成账单 每个月1号生成上个月的账单
     * @param string $billDate 账单日期
     */
    public static function sysCreateBills($billDate)
    {
        $billDate = $billDate ? $billDate : date('Y-m', strtotime('-1 months'));
        $billDate = $billDate . '-01';
        $startTime = $billDate;
        $endTime = date('Y-m', strtotime('+1 month', strtotime($startTime)));
        $endTime = $endTime . '-01';
        $where = [
            'transaction_type[!]' => [mSettlement::TRANSACTION_TYPE_WITHDRAWAL, mSettlement::TRANSACTION_TYPE_DEPOSIT_ADD_GOODS_AMOUNT, mSettlement::TRANSACTION_TYPE_HHZ_ALLOWANCE_ADJUSTMENT],
            'operate_time[>=]' => $startTime,
            'operate_time[<]' => $endTime,
        ];
        $list = mSettlement::getTransList('*', $where);
        if (!$list) {
            return false;
        }
        $date = date('Y-m-d H:i:s');
        $data = [];
        foreach ($list as $row) {
            $shopId = $row['shop_id'];
            if (in_array($row['pay_way'], PayConfig::_ALIPAY_PAY_WAYS_LIST)) {
                $channel = mBills::CHANNEL_ALIPAY;
            } else if (in_array($row['pay_way'], PayConfig::_WECHAT_PAY_WAYS_LIST)) {
                $channel = mBills::CHANNEL_WECHAT;
            } else if ($row['pay_way'] == PayConfig::_PAY_WAYS_CUSTOMIZE_PAY_WAY) {//线下支付
                $channel = mBills::CHANNEL_CUSTOMIZE;
            } else {
                $channel = mBills::CHANNEL_OTHER;
            }
            //店铺账单按渠道
            if (!isset($data[$shopId][$channel])) {
                $data[$shopId][$channel] = [
                    'shop_id' => $shopId,
                    'bill_date' => $billDate,
                    'channel' => $channel,
                    'status' => mBills::STATUS_NORMAL,
                    'goods_amount' => 0,
                    'shipping_amount' => 0,
                    'commissions' => 0,
                    'integral_amount' => 0,
                    'coupon_amount' => 0,
                    'activity_allowance' => 0,//平台活动补贴
                    'packet_amount' => 0,
                    'pay_amount' => 0,
                    'settlement_goods_amount' => 0,
                    'settlement_amount' => 0,
                    'addtime' => $date,
                ];
            }
            if ($row['order_no']) {
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_RMB) {
                    $data[$shopId][$channel]['shipping_amount'] = bcadd($data[$shopId][$channel]['shipping_amount'], $row['shipping_amount'], 2);
                }
                if ($row['transaction_type'] == mSettlement::TRANSACTION_TYPE_ORDER_REFUND) {
                    $data[$shopId][$channel]['shipping_amount'] = bcsub($data[$shopId][$channel]['shipping_amount'], $row['shipping_amount'], 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_RMB, mSettlement::TRANSACTION_TYPE_ORDER_REFUND])) {
                    $data[$shopId][$channel]['pay_amount'] = bcadd($data[$shopId][$channel]['pay_amount'], $row['transaction_amount'], 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_DEDUCT_COMMISSION, mSettlement::TRANSACTION_TYPE_BACK_COMMISSION])) {
                    $data[$shopId][$channel]['commissions'] = bcadd($data[$shopId][$channel]['commissions'], bcsub(0, $row['transaction_amount'], 2), 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_INTEGRAL, mSettlement::TRANSACTION_TYPE_BACK_INTEGRAL])) {
                    $data[$shopId][$channel]['integral_amount'] = bcadd($data[$shopId][$channel]['integral_amount'], $row['transaction_amount'], 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_COUPON, mSettlement::TRANSACTION_TYPE_HHZ_BACK_COUPON])) {
                    $data[$shopId][$channel]['coupon_amount'] = bcadd($data[$shopId][$channel]['coupon_amount'], $row['transaction_amount'], 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_ACTIVITY_ALLOWANCE, mSettlement::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE])) {
                    $data[$shopId][$channel]['activity_allowance'] = bcadd($data[$shopId][$channel]['activity_allowance'], $row['transaction_amount'], 2);
                }
                if (in_array($row['transaction_type'], [mSettlement::TRANSACTION_TYPE_ORDER_SETTLEMENT_RED_PACKET, mSettlement::TRANSACTION_TYPE_BACK_RED_PACKET])) {
                    $data[$shopId][$channel]['packet_amount'] = bcadd($data[$shopId][$channel]['packet_amount'], $row['transaction_amount'], 2);
                }
                $data[$shopId][$channel]['settlement_amount'] = bcadd($data[$shopId][$channel]['settlement_amount'], $row['transaction_amount'], 2);
            } else {
                $data[$shopId][$channel]['commissions'] = bcadd($data[$shopId][$channel]['commissions'], $row['transaction_amount'], 2);
            }

        }
        $channelAll = mBills::CHANNEL_ALL;
        foreach ($data as $shopId => &$channelList) {
            if (!isset($channelList[$channelAll])) {
                $channelList[$channelAll] = [
                    'shop_id' => $shopId,
                    'bill_date' => $billDate,
                    'channel' => $channelAll,
                    'status' => mBills::STATUS_NORMAL,
                    'goods_amount' => 0,
                    'shipping_amount' => 0,
                    'commissions' => 0,
                    'integral_amount' => 0,
                    'coupon_amount' => 0,
                    'activity_allowance' => 0,
                    'packet_amount' => 0,
                    'pay_amount' => 0,
                    'settlement_goods_amount' => 0,
                    'settlement_amount' => 0,
                    'addtime' => $date,
                ];
            }
            foreach ($channelList as $channel => &$row) {
                if ($channel == mBills::CHANNEL_ALL) {
                    continue;
                }
                if (in_array($channel, [mBills::CHANNEL_WECHAT, mBills::CHANNEL_ALIPAY, mBills::CHANNEL_CUSTOMIZE])) {
                    $row['commissions'] = $data[$shopId][$channel]['commissions'];
                    $row['goods_amount'] = $data[$shopId][$channel]['pay_amount'] + $data[$shopId][$channel]['integral_amount'] + $data[$shopId][$channel]['coupon_amount'] + $data[$shopId][$channel]['packet_amount'] - $data[$shopId][$channel]['shipping_amount'];
                    $row['settlement_goods_amount'] = $data[$shopId][$channel]['goods_amount'] + $data[$shopId][$channel]['activity_allowance'] - $data[$shopId][$channel]['commissions'];
                } else if ($channel == mBills::CHANNEL_OTHER) {
                    $row['settlement_amount'] = $data[$shopId][$channel]['commissions'];
                    $row['settlement_goods_amount'] = $data[$shopId][$channel]['settlement_amount'];
                }
                $channelList[$channelAll]['goods_amount'] = bcadd($channelList[$channelAll]['goods_amount'], $row['goods_amount'], 2);
                $channelList[$channelAll]['shipping_amount'] = bcadd($channelList[$channelAll]['shipping_amount'], $row['shipping_amount'], 2);
                $channelList[$channelAll]['commissions'] = bcadd($channelList[$channelAll]['commissions'], $row['commissions'], 2);
                $channelList[$channelAll]['integral_amount'] = bcadd($channelList[$channelAll]['integral_amount'], $row['integral_amount'], 2);
                $channelList[$channelAll]['coupon_amount'] = bcadd($channelList[$channelAll]['coupon_amount'], $row['coupon_amount'], 2);
                $channelList[$channelAll]['activity_allowance'] = bcadd($channelList[$channelAll]['activity_allowance'], $row['activity_allowance'], 2);
                $channelList[$channelAll]['packet_amount'] = bcadd($channelList[$channelAll]['packet_amount'], $row['packet_amount'], 2);
                $channelList[$channelAll]['pay_amount'] = bcadd($channelList[$channelAll]['pay_amount'], $row['pay_amount'], 2);
                $channelList[$channelAll]['settlement_goods_amount'] = bcadd($channelList[$channelAll]['settlement_goods_amount'], $row['settlement_goods_amount'], 2);
                $channelList[$channelAll]['settlement_amount'] = bcadd($channelList[$channelAll]['settlement_amount'], $row['settlement_amount'], 2);
            }
        }
        $billData = $data;
        $result = mBills::action(function () use ($billData) {
            $insertData = [];
            foreach ($billData as $shopId => $channels) {
                if (isset($channels[mBills::CHANNEL_ALL])) {
                    $parentId = mBills::insertData($channels[mBills::CHANNEL_ALL]);
                    $res = mBillOperateLog::addData([
                        'bill_id' => $parentId,
                        'shop_id' => $shopId,
                        'bill_date' => $channels[mBills::CHANNEL_ALL]['bill_date'],
                        'operate_type' => mBillOperateLog::TYPE_OPERATE_CREATE,
                        'remark' => mBillOperateLog::$operateTypeDesc[mBillOperateLog::TYPE_OPERATE_CREATE],
                        'operate_user_type' => mBillOperateLog::USER_OPERATE_SYS,
                        'operate_user_id' => 0
                    ]);
                    if (!$res) {
                        throw new \Exception('生成账单失败,写账单日志失败');
                    }
                    foreach ($channels as $row) {
                        if ($row['channel'] != mBills::CHANNEL_ALL) {
                            $row['parent_id'] = $parentId;
                            $insertData[] = $row;
                        }
                    }
                } else {
                    throw new \Exception('生成账单失败');
                }
            }
            if ($insertData) {
                $ret = mBills::addData($insertData);
                return $ret;
            } else {
                Queue::insertQueue("queue/Wechatnotice/toTag", [
                    "totag" => \Biz\pockets\constInterface\BizWechatTagConfig::TAG_BIZ_RD_SYSTEM,
                    "content" => "生成账单失败:#inserData:" . json_encode($insertData, JSON_UNESCAPED_UNICODE)
                ], Queue::QUEUE_BIZ);
                return false;
            }
        });
        return $result;
    }

    /**
     * 获取账单列表
     * @param string $billDate
     * @param int $shopId
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function admGetBillList($billDate = '', $shopId = 0, $page = 0, $pageSize = 0, $status = null)
    {
        $limit = [];
        if ($page && $pageSize) {
            $limit = [($page - 1) * $pageSize, $pageSize];
        }
        if (!is_null($status)) {
            $where['status'] = $status;
        }
        if ($billDate) {
            $where['bill_date'] = $billDate;
        }
        if ($shopId) {
            $where['shop_id'] = $shopId;
        }
        $where['channel'] = mBills::CHANNEL_ALL;
        $count = mBills::getBillListCount($where);
        $billIds = mBills::getBillList('id', $where, $limit, ['bill_date' => 'DESC', 'id' => 'DESC']);
        $result = [];
        if ($billIds) {
            $where = ['OR' => ['parent_id' => $billIds, 'id' => $billIds]];
            if (!is_null($status)) {
                $where['status'] = $status;
            }
            $result = mBills::getBillList('*', $where, [], ['channel' => 'ASC']);
        }
        $list = [];
        $shopInfos = [];
        if ($result) {
            $shopIds = array_column($result, 'shop_id');
            $userInfos = User::getUsers($shopIds);
            $shopBasicInfos = mShopBasicInfo::getInfoList($shopIds);
            $shopCommanyInfos = mShopCompanyInfo::getInfoList($shopIds);
            $shopPersonalInfos = mShopPersonalInfo::getInfoList($shopIds);
            $shopBankInfos = mShopBankInfo::getInfoList($shopIds);
            foreach ($result as $row) {
                $shopId = $row['shop_id'];
                if (!isset($shopInfos[$shopId])) {
                    $shopInfos[$shopId] = [
                        'name' => ($shopBasicInfos[$shopId]['type'] == mShopBasicInfo::TYPE_COMPANY) ? $shopCommanyInfos[$shopId]['name'] : $shopPersonalInfos[$shopId]['realname'],
                        'bank_name' => $shopBankInfos[$shopId]['bank_name'],
                        'bank_no' => $shopBankInfos[$shopId]['bank_no']
                    ];
                }
                $billDate = $row['bill_date'];
                $list[$shopId][$billDate]['shop_id'] = $shopId;
                $list[$shopId][$billDate]['shop_name'] = $userInfos[$shopId]['nick'];
                $list[$shopId][$billDate]['bill_Date'] = $billDate;
                if ($row['channel'] == mBills::CHANNEL_ALL) {
                    $list[$shopId][$billDate] = [
                        'bill_id' => $row['id'],
                        'channel' => $row['channel'],
                        'channel_desc' => mBills::$channelDesc[$row['channel']],
                        'goods_amount' => $row['goods_amount'],
                        'shipping_amount' => $row['shipping_amount'],
                        'commissions' => $row['commissions'],
                        'integral_amount' => $row['integral_amount'],
                        'coupon_amount' => $row['coupon_amount'],
                        'activity_allowance' => $row['activity_allowance'],
                        'packet_amount' => $row['packet_amount'],
                        'other_amount' => $row['other_amount'],
                        'pay_amount' => $row['pay_amount'],
                        'settlement_goods_amount' => $row['settlement_goods_amount'],
                        'settlement_amount' => $row['settlement_amount'],
                        'status' => $row['status'],
                        'status_desc' => mBills::$statusDesc[$row['status']],
                    ];
                } else {
                    $list[$shopId][$billDate]['channels'][$row['channel']] = [
                        'channel' => $row['channel'],
                        'channel_desc' => mBills::$channelDesc[$row['channel']],
                        'goods_amount' => $row['goods_amount'],
                        'shipping_amount' => $row['shipping_amount'],
                        'commissions' => $row['commissions'],
                        'integral_amount' => $row['integral_amount'],
                        'coupon_amount' => $row['coupon_amount'],
                        'activity_allowance' => $row['activity_allowance'],
                        'packet_amount' => $row['packet_amount'],
                        'other_amount' => $row['other_amount'],
                        'pay_amount' => $row['pay_amount'],
                        'settlement_goods_amount' => $row['settlement_goods_amount'],
                        'settlement_amount' => $row['settlement_amount'],
                    ];
                }
            }
        }
        return ['list' => $list, 'shopInfos' => $shopInfos, 'count' => $count];
    }

    public static function adminExportBills($data)
    {
        $array = [];
        $headers = [
            ['账单编号', '商家id', '商家名称', '账单月份', '渠道', '商品金额', '运费', '平台补贴', '平台补贴', '平台补贴', '平台补贴', '平台补贴', '买家实付', '佣金', '应结商品款', '应转账金额', '账单状态'],
            ['账单编号', '商家id', '商家名称', '账单月份', '渠道', '商品金额', '运费', '积分', '优惠券', '活动', '红包', '其他', '买家实付', '佣金', '应结商品款', '应转账金额', '账单状态'],
        ];
        $mergeHeader = ['A1:A2', 'B1:B2', 'C1:C2', 'D1:D2', 'E1:E2', 'F1:F2', 'G1:G2', 'H1:L1', 'M1:M2', 'N1:N2', 'O1:O2', 'P1:P2', 'Q1:Q2'];
        $letter = range('A', 'Q');
        $excelObj = new DownExcel();
        $excelObj->setFileName('商家结算' . time());
        $excelObj->setSheetTitle('商家结算');
        //设置列宽
        $widths = [
            'A' => 15,
            'B' => 20,
            'C' => 30,
            'D' => 20,
            'E' => 20,
            'F' => 25,
            'G' => 25,
            'H' => 25,
            'I' => 25,
            'J' => 30,
            'K' => 30,
            'L' => 30,
            'M' => 30,
            'N' => 30,
            'O' => 30,
            'P' => 30,
            'Q' => 30,
        ];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        $excelObj->freezePane('A1');//冻结窗口

        //设置货币形式
        $excelObj->setMoneyFloatFormat('F');
        $excelObj->setMoneyFloatFormat('G');
        $excelObj->setMoneyFloatFormat('H');
        $excelObj->setMoneyFloatFormat('I');
        $excelObj->setMoneyFloatFormat('J');
        $excelObj->setMoneyFloatFormat('K');
        $excelObj->setMoneyFloatFormat('L');
        $excelObj->setMoneyFloatFormat('M');
        $excelObj->setMoneyFloatFormat('N');
        $excelObj->setMoneyFloatFormat('O');
        $excelObj->setMoneyFloatFormat('P');
        array_walk($data, function ($v) use (&$array, &$mergeCount) {
            foreach ($v['channels'] as $val) {
                $array[] = [
                    'parent_id' => $v['bill_id'],
                    'shop_id' => $v['shop_id'],
                    'shop_name' => $v['shop_name'],
                    'bill_date' => $v['bill_Date'],
                    'channel_desc' => $val['channel_desc'],
                    'goods_amount' => $val['goods_amount'],
                    'shipping_amount' => $val['shipping_amount'],
                    'integral_amount' => $val['integral_amount'],
                    'coupon_amount' => $val['coupon_amount'],
                    'activity_allowance' => $val['activity_allowance'],
                    'packet_amount' => $val['packet_amount'],
                    'other_amount' => $val['other_amount'],
                    'pay_amount' => $val['pay_amount'],
                    'commissions' => $val['commissions'],
                    'settlement_goods_amount' => $val['settlement_goods_amount'],
                    'settlement_amount' => $val['settlement_amount'],
                    'status' => $v['status_desc']
                ];
            }
        });

        foreach ($letter as $key => $value) {
            //设置字体
            $excelObj->setFrontName($value);
            //设置加粗
            $excelObj->setFrontBold($value . '1');
            $excelObj->setFrontBold($value . '2');
            //设置大小
            $excelObj->setFrontSize($value);
            //保护单元格
            //$excelObj->protectCells($value . '1:' . $value . (count($array) + 1));
        }
        $excelObj->create($array, $headers, null, '', [], [], $mergeHeader);
    }

    public static function adminExportWillTransBills($status, $billDate = '', $shopId = 0)
    {
        $where['status'] = $status;
        if ($billDate) {
            $where['bill_date'] = $billDate;
        }
        if ($shopId) {
            $where['shop_id'] = $shopId;
        }
        $where['channel'] = mBills::CHANNEL_ALL;
        $result = mBills::getBillList('*', $where, [], ['bill_date' => 'DESC']);
        $shopIds = array_column($result, 'shop_id');
        $userInfos = User::getUsers($shopIds);
        $shopBasicInfos = mShopBasicInfo::getInfoList($shopIds);
        $shopCommanyInfos = mShopCompanyInfo::getInfoList($shopIds);
        $shopPersonalInfos = mShopPersonalInfo::getInfoList($shopIds);
        $shopBankInfos = mShopBankInfo::getInfoList($shopIds);
        $shopInvoiceInfos = mShopInvoiceInformation::getInvoiceInfoList($shopIds);
        $data = [];
        foreach ($result as $row) {
            if (!in_array($row['shop_id'], GoodsBasicInfo::$testShopIds)) {//测试店铺
                $array = [];
                $array['shop_id'] = $row['shop_id'];
                $array['shop_name'] = $userInfos[$row['shop_id']]['nick'];
                $array['bill_date'] = date('Y.m', strtotime($row['bill_date']));
                $array['type'] = ($shopBasicInfos[$row['shop_id']]['type'] == mShopBasicInfo::TYPE_COMPANY) ? '公司' : '个人';
                $array['settlement_amount'] = $row['settlement_amount'];
                $array['commissions'] = $row['commissions'];
                $array['bank_user_name'] = ($shopBasicInfos[$row['shop_id']]['type'] == mShopBasicInfo::TYPE_COMPANY) ? $shopCommanyInfos[$row['shop_id']]['name'] : $shopPersonalInfos[$row['shop_id']]['realname'];
                $array['bank_name'] = $shopBankInfos[$row['shop_id']]['bank_name'] . ' ' . $shopBankInfos[$row['shop_id']]['bank_subbranch'];
                $array['bank_no'] = $shopBankInfos[$row['shop_id']]['bank_no'];
                $invoiceType = '';
                $invoiceName = '';
                $invoiceNo = '';
                $invoiceAddress = '';
                $invoiceBank = '';
                $sendAddress = '';
                $sendPersonal = '';
                $sendPhone = '';
                if (isset($shopInvoiceInfos[$row['shop_id']])) {
                    $shopInvoiceInfo = $shopInvoiceInfos[$row['shop_id']];
                    $invoiceType = mShopInvoiceInformation::$typeDesc[$shopInvoiceInfo['type']];
                    $invoiceName = $shopInvoiceInfo['name'];
                    $invoiceNo = $shopInvoiceInfo['taxpayer_identification_number'];
                    $invoiceAddress = $shopInvoiceInfo['company_address'] . ' ' . $shopInvoiceInfo['company_phone'];
                    $invoiceBank = $shopInvoiceInfo['bank_name'] . ' ' . $shopInvoiceInfo['bank_no'];
                    $sendAddress = $shopInvoiceInfo['send_address_str'] . $shopInvoiceInfo['send_address'];
                    $sendPersonal = $shopInvoiceInfo['consignee_name'];
                    $sendPhone = $shopInvoiceInfo['consignee_phone'];
                }
                $array['invoice_type'] = $invoiceType;
                $array['invoice_name'] = $invoiceName;
                $array['invoice_no'] = $invoiceNo;
                $array['invoice_address'] = $invoiceAddress;
                $array['invoice_bank'] = $invoiceBank;
                $array['send_address'] = $sendAddress;
                $array['send_personal'] = $sendPersonal;
                $array['send_phone'] = $sendPhone;
                $data[] = $array;
            }
        }
        $headers = [
            ['', '', '', '', '', '', '转账信息', '转账信息', '转账信息', '开票信息', '开票信息', '开票信息', '开票信息', '开票信息', '发票邮寄信息', '发票邮寄信息', '发票邮寄信息'],
            ['商家uid', '商家名称', '账单月份', '店铺类型', '应转账金额', '佣金', '账户名', '开户银行', '账号', '纳税人类型', '名称', '纳税人识别号', '地址+电话', '开户行+账号', '邮寄地址', '收件人', '联系方式'],
        ];
        $mergeHeader = ['G1:I1', 'J1:N1', 'O1:Q1'];
        $letter = range('A', 'Q');
        $excelObj = new DownExcel();
        $excelObj->setFileName('转账和开票数据' . date('Y-m-d'));
        $excelObj->setSheetTitle('转账和开票数据' . date('Y-m-d'));
        //设置列宽
        $widths = [
            'A' => 15,
            'B' => 30,
            'C' => 20,
            'D' => 20,
            'E' => 25,
            'F' => 25,
            'G' => 25,
            'H' => 30,
            'I' => 30,
            'J' => 30,
            'K' => 30,
            'L' => 30,
            'M' => 30,
            'N' => 30,
            'O' => 30,
            'P' => 30,
            'Q' => 30,
        ];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();

        foreach ($letter as $key => $value) {
            //设置字体
            $excelObj->setFrontName($value);
            //设置加粗
            $excelObj->setFrontBold($value . '1');
            $excelObj->setFrontBold($value . '2');
            //设置大小
            $excelObj->setFrontSize($value);
            //保护单元格
            //$excelObj->protectCells($value . '1:' . $value . (count($array) + 1));
        }
        $excelObj->create($data, $headers, null, '', [], [], $mergeHeader, ['I', 'L', 'M', 'N']);
    }

    /**
     * 管理员更改账单状态
     * @param $billId
     * @param $shopId
     * @param $billDate
     * @param $status
     * @param $operateType
     * @param $adminId
     * @param $amount
     * @param $transNumber
     * @param $remark
     * @return mixed
     */
    public static function admChangeBillStatus($billId, $shopId, $billDate, $status, $operateType, $adminId, $amount = 0, $transNumber = '', $remark = '')
    {
        $result = mBills::action(function () use ($billId, $shopId, $billDate, $status, $operateType, $adminId, $amount, $transNumber, $remark) {
            $where = ['OR' => ['id' => $billId, 'parent_id' => $billId]];
            $data['status'] = $status;
            if ($status == mBills::STATUS_TRANSFER_SUCCESS) {
                $data['transfer_admin_id'] = $adminId;
                $data['transfer_time'] = date('Y-m-d H:i:s');
            }
            $res = mBills::changeBills($data, $where);
            if ($res) {
                $res = mBillOperateLog::addData([
                    'bill_id' => $billId,
                    'shop_id' => $shopId,
                    'bill_date' => $billDate,
                    'operate_type' => $operateType,
                    'trans_amount' => $amount,
                    'remark' => $remark ? $remark : mBillOperateLog::$operateTypeDesc[$operateType],
                    'trans_number' => $transNumber,
                    'operate_user_type' => mBillOperateLog::USER_OPERATE_ADMIN,
                    'operate_user_id' => $adminId
                ]);
                if (!$res) {
                    throw new \Exception('更改账单状态失败,写入操作日志失败');
                }
            } else {
                throw new \Exception('更改账单状态失败');
            }
            return true;
        });
        return $result;
    }

    /**
     * 管理后台获取账户交易明细
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @param int $page
     * @param int $pageSize
     * @return mixed
     */
    public static function admGetTransactionList($shopId, $startTime, $endTime, $orderNo, $page = 1, $pageSize = 20)
    {
        $limit = [($page - 1) * $pageSize, $pageSize];
        $list = mSettlement::getTransactionList($shopId, $startTime, $endTime, $orderNo, $limit, ['id' => 'DESC']);
        $shopIds = array_column($list, 'shop_id');
        $shopIds = array_unique($shopIds);
        $userInfos = User::getUsers($shopIds);
        $adminUids = [];
        foreach ($list as &$row) {
            $row['shop_name'] = $userInfos[$row['shop_id']]['nick'];
            if ($row['operate_type'] == mSettlement::OPERATE_TYPE_ADMIN) {
                array_push($adminUids, $row['operate_admin_id']);
            }
        }
        if ($adminUids) {
            $adminUsers = mShopAdminUser::getAdminList($adminUids);
            foreach ($list as &$row) {
                if ($row['operate_type'] == mSettlement::OPERATE_TYPE_ADMIN) {
                    $row['operator'] = isset($adminUsers[$row['operate_admin_id']]['name']) ? 'admin:' . $adminUsers[$row['operate_admin_id']]['name'] : 'admin:' . $row['operate_admin_id'];
                }
            }
        }
        $count = mSettlement::getTransactionCount($shopId, $startTime, $endTime, $orderNo);
        return ['list' => $list, 'count' => $count];
    }

    public static function admGetConfirmOrderList($shopId, $startTime, $endTime, $page = 1, $pageSize = 20)
    {
        $list = self::getConfirmOrderList($shopId, $startTime, $endTime, $page, $pageSize);
        $count = self::getConfirmOrderCount($shopId, $startTime, $endTime);
        return ['list' => $list, 'count' => $count];
    }

    public static function admGetRefundOrderList($shopId, $startTime, $endTime, $page = 1, $pageSize = 20)
    {
        $result = self::getRefundOrderList($shopId, $startTime, $endTime, $page, $pageSize);
        return $result;
    }

    /**
     * 运营调整(调整佣金 or 调整平台补贴(其他))
     * @param $shopId
     * @param $billDate
     * @param $channel
     * @param $amount
     * @param $operateType
     * @return bool
     * @throws \Exception
     */
    public static function admUpdateBillAccount($shopId, $billDate, $channel, $amount, $operateType)
    {
        $res = mBills::getBillList('*', ['shop_id' => $shopId, 'bill_date' => $billDate]);
        $billList = [];
        foreach ($res as $row) {
            $billList[$row['channel']] = $row;
        }
        if ($billList && $billList[mBills::CHANNEL_ALL]) {
            $amount = floatval($amount);
            $result = mBills::action(function () use ($billList, $shopId, $billDate, $channel, $amount, $operateType) {
                $commissions = 0;
                $otherAmount = 0;
                if ($operateType == mBillOperateLog::TYPE_OPERATE_COMMISSION_ADJUSTMENT) {//运营调整-调整佣金 （佣金）
                    $commissions = 0 - $amount;//调整佣金补贴，相当于好好住少收商家佣金，故是负数
                } else if ($operateType == mBillOperateLog::TYPE_OPERATE_HHZ_ALLOWANCE_ADJUSTMENT) {//运营调整-调整平台补贴 (其他)
                    $otherAmount = $amount;//调整平台补贴，相当于好好住给商家结算账户加钱，故是正数
                }
                $parentBillInfo = $billList[mBills::CHANNEL_ALL];
                if (isset($billList[$channel])) {
                    $billInfo = $billList[$channel];
                    $allCommissions = floatval($billInfo['commissions']) + floatval($commissions);
                    $allOtherAmount = floatval($billInfo['other_amount']) + floatval($otherAmount);
                    $data['commissions'] = $allCommissions;
                    $data['other_amount'] = $allOtherAmount;
                    $data['settlement_amount'] = floatval($billInfo['settlement_amount']) - floatval($commissions) + floatval($otherAmount);
                    $data['settlement_goods_amount'] = floatval($billInfo['settlement_goods_amount']) - floatval($commissions) + floatval($otherAmount);
                    $result = mBills::updateData($data, ['shop_id' => $shopId, 'bill_date' => $billDate, 'channel' => $channel]);
                } else {
                    $goodsAmount = 0;
                    $data = [
                        'shop_id' => $shopId,
                        'bill_date' => $billDate,
                        'channel' => $channel,
                        'status' => mBills::STATUS_NORMAL,
                        'goods_amount' => 0,
                        'shipping_amount' => 0,
                        'commissions' => $commissions,
                        'integral_amount' => 0,
                        'coupon_amount' => 0,
                        'other_amount' => $otherAmount,
                        'pay_amount' => 0,
                        'settlement_goods_amount' => $goodsAmount - floatval($commissions) + floatval($otherAmount),//商品金额-佣金+补贴
                        'settlement_amount' => 0 - floatval($commissions) + floatval($otherAmount),
                        'addtime' => date('Y-m-d H:i:s'),
                        'parent_id' => $parentBillInfo['id'],
                    ];
                    $result = mBills::insertData($data);
                }
                if ($result) {
                    $allCommissions = floatval($parentBillInfo['commissions']) + floatval($commissions);
                    $allOtherAmount = floatval($parentBillInfo['other_amount']) + floatval($otherAmount);
                    $parentdata = [
                        'commissions' => $allCommissions,
                        'other_amount' => $allOtherAmount,
                        'settlement_amount' => floatval($parentBillInfo['settlement_amount']) - floatval($commissions) + floatval($otherAmount),
                        'settlement_goods_amount' => floatval($parentBillInfo['settlement_goods_amount']) - floatval($commissions) + floatval($otherAmount)
                    ];
                    $result = mBills::updateData($parentdata, ['shop_id' => $shopId, 'bill_date' => $billDate, 'channel' => mBills::CHANNEL_ALL]);
                    return $result;
                }
                return false;
            });
            if ($result) {
                return true;
            }
            return false;
        } else {
            throw new \Exception('数据错误');
        }
    }

    /**
     * 平台补贴
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @param $transactionType
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function admGetAllowanceList($shopId, $startTime, $endTime, $orderNo, $transactionType, $page = 1, $pageSize = 20)
    {
        $limit = [($page - 1) * $pageSize, $pageSize];
        $list = mHhzConst::getTransactionList($shopId, $startTime, $endTime, $orderNo, $transactionType, $limit);
        $shopId = array_column($list, 'shop_id');
        $userInfos = User::getUsers($shopId);
        foreach ($list as &$row) {
            $row['shop_name'] = $userInfos[$row['shop_id']]['nick'];
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_INTEGRAL_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_BACK_INTEGRAL])) {
                $row['allowance_type'] = '积分';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_HHZ_COUPON_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_HHZ_BACK_COUPON])) {
                $row['allowance_type'] = '优惠券';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_HHZ_ACTIVITY_ALLOWANCE_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE])) {
                $row['allowance_type'] = '平台活动补贴';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_RED_PACKET_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_BACK_RED_PACKET])) {
                $row['allowance_type'] = '现金红包';
            }
        }
        $count = mHhzConst::getTransactionCount($shopId, $startTime, $endTime, $orderNo, $transactionType);
        return ['list' => $list, 'count' => $count];
    }

    /**
     * 导出平台补贴
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @param $transactionType
     * @return bool
     */
    public static function admExportAllowanceList($shopId, $startTime, $endTime, $orderNo, $transactionType)
    {
        $list = mHhzConst::getTransactionList($shopId, $startTime, $endTime, $orderNo, $transactionType);
        if (!$list) {
            return false;
        }
        $shopId = array_column($list, 'shop_id');
        $userInfos = User::getUsers($shopId);
        foreach ($list as &$row) {
            $row['shop_name'] = $userInfos[$row['shop_id']]['nick'];
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_INTEGRAL_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_BACK_INTEGRAL])) {
                $row['allowance_type'] = '积分';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_HHZ_COUPON_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_HHZ_BACK_COUPON])) {
                $row['allowance_type'] = '优惠券';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_HHZ_ACTIVITY_ALLOWANCE_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_HHZ_BACK_ACTIVITY_ALLOWANCE])) {
                $row['allowance_type'] = '平台活动补贴';
            }
            if (in_array($row['transaction_type'], [BaseAccountTransaction::TRANSACTION_TYPE_RED_PACKET_PAYMENT, BaseAccountTransaction::TRANSACTION_TYPE_BACK_RED_PACKET])) {
                $row['allowance_type'] = '现金红包';
            }
        }
        $data = [];
        foreach ($list as $rows) {
            $data[] = [
                'id' => $rows['id'],
                'operate_time' => $rows['operate_time'],
                'transaction_type_desc' => $rows['transaction_type_desc'],
                'transaction_amount' => $rows['transaction_amount'],
                'order_no' => $rows['order_no'],
                'shop_name' => $rows['shop_id'] . "\n" . $rows['shop_name'],
                'allowance_type' => $rows['allowance_type'],
                'remark' => $rows['remark'],
            ];
        }

        $header = ['交易编号', '交易时间', '交易类型', '交易金额', '订单号', '补贴商家', '补贴方式', '备注'];
        $letter = range('A', 'H');
        $excelObj = new DownExcel();
        $excelObj->setFileName('平台补贴' . time());
        $excelObj->setSheetTitle('平台补贴');
        //设置列宽
        $widths = ['A' => 18, 'B' => 25, 'C' => 20, 'D' => 30, 'E' => 40, 'F' => 30, 'G' => 20, 'H' => 50];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        $excelObj->freezePane('A1');//冻结窗口
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        $excelObj->create($data, $header);

    }

    /**
     * 导出商城交易台账
     * @param $timeType 1：订单支付时间查询 2：确认收货时间查询
     * @param $startTime
     * @param $endTime
     * @param $adminEmail
     */
    public static function admExportOrderList($timeType, $startTime, $endTime, $adminEmail)
    {
        ini_set('memory_limit', '-1');
        if ($timeType && $startTime && $endTime) {
            if ($timeType == 1) {
                $where = [
                    'pay_time[>=]' => $startTime,
                    'pay_time[<=]' => $endTime
                ];
            } else {
                $where = [
                    'confirm_time[>=]' => $startTime,
                    'confirm_time[<=]' => $endTime
                ];
            }
            $orderResult = mOrder::getOrderInfoList($where);
            if ($orderResult) {
                $orderList = [];
                $orderNos = [];
                foreach ($orderResult as $row) {
                    array_push($orderNos, $row['order_no']);
                    $orderList[$row['order_no']] = $row;
                }
                $orderNos = array_column($orderResult, 'order_no');
                $orderGoodsResult = mOrderGoods::select('*', ['order_no' => $orderNos]);
                $orderExtendResult = msOrderExtend::getOrderListByOrderNos($orderNos);
                $storeIds = array_column($orderExtendResult, 'store_id');
                $storeIds = array_filter($storeIds);
                $storeResult = mStore::getStoresByIds($storeIds);
                $storeList = [];
                foreach ($storeResult as $row) {
                    $storeList[$row['id']] = $row['title'];
                }
                $orderExtendList = [];
                foreach ($orderExtendResult as $row) {
                    $row['store'] = '';
                    if (isset($storeList[$row['store_id']])) {
                        $row['store'] = $storeList[$row['store_id']];
                    }
                    $orderExtendList[$row['order_no']] = $row;
                }

                $orderGoodsList = [];
                $goodsIds = [];
                foreach ($orderGoodsResult as $row) {
                    array_push($goodsIds, $row['goods_id']);
                    $orderGoodsList[$row['order_no']][$row['sku_id']] = $row;
                }
                $goodsResult = mGoodsBasicInfo::select(['goods_id', 'category_id', 'second_category_id', 'third_category_id', 'brand_id'], ['goods_id' => $goodsIds]);
                $brandIds = array_column($goodsResult, 'brand_id');
                $brandIds = array_unique(array_filter($brandIds));
                $brandList = mBrand::getBrandListByIds($brandIds);
                $goodsList = [];
                foreach ($goodsResult as $row) {
                    $row['brand_name'] = ($row['brand_id'] && isset($brandList[$row['brand_id']])) ? $brandList[$row['brand_id']]['brand_name'] : '';
                    $goodsList[$row['goods_id']] = $row;
                }
                $categoryList = [];
                $categoryResult = mCategory::select('*');
                if (!empty($categoryResult)) {
                    foreach ($categoryResult as $value) {
                        $categoryList[$value['id']] = $value['cate_name'];
                    }
                }
                $orderRefundList = [];
                $orderRefundResult = mOrderRefund::getRefundListByOrderNosByStatus($orderNos, mOrderRefund::STATUS_COMPLETE);
                foreach ($orderRefundResult as $row) {
                    $orderRefundList[$row['order_no']][$row['refund_order_no']] = $row;
                }
                $partOutOrderList = [];
                $partOutOrderResult = mOrderPackage::select('*', ['order_no' => $orderNos]);
                foreach ($partOutOrderResult as $row) {
                    $partSkuIds = explode(',', $row['sku_id_list']);
                    foreach ($partSkuIds as $skuId) {
                        $partOutOrderList[$row['order_no']][$skuId] = $row;
                    }
                }
                $customizePayList = [];
                $customizePayResult = mCustomizePayWay::select(['order_no', 'pay_type', 'pay_amount', 'remark'], ['order_no' => $orderNos]);
                foreach ($customizePayResult as $row) {
                    $payType = $row['pay_type'];
                    if ($payType) {
                        $customizePayList[$row['order_no']]['pay_way'] = mCustomizePayWay::PAY_TYPE_DESC[$payType];
                    }
                    $customizePayList[$row['order_no']]['pay_type'] = $row['pay_type'];
                    $customizePayList[$row['order_no']]['pay_amount'] = $row['pay_amount'];
                    $customizePayList[$row['order_no']]['remark'] = $row['remark'];
                }
                $skuIds = [];
                $dataArr = [];
                //$i = 1;
                foreach ($orderGoodsList as $orderNo => $list) {
                    $goodsShippingAmounts = array_column(array_values($list), 'shipping_amount');
                    $allGoodsShippingAmount = !empty($goodsShippingAmounts) ? array_sum($goodsShippingAmounts) : 0;
                    $address = ShopArea::locationId2String($orderList[$orderNo]['consignee_province'] . ',' . $orderList[$orderNo]['consignee_city']);
                    $store = isset($orderExtendList[$orderNo]) ? $orderExtendList[$orderNo]['store'] : '北京';//订单默认归属北京
                    $payWay = '';
                    $customizePayAmount = '';
                    $customizeRemark = '';
                    preg_match('/^BJ./', $orderNo, $res);
                    if ($res) {
                        if (isset($customizePayList[$orderNo])) {
                            // 北京祥云小镇只是 “北京pos-xxx”和北京现金
                            if (!in_array($customizePayList[$orderNo]['pay_type'], [mCustomizePayWay::PAY_TYPE_BJ_POS_ALIPAY, mCustomizePayWay::PAY_TYPE_BJ_POS_BANKCARD, mCustomizePayWay::PAY_TYPE_BJ_POS_WECHAT, mCustomizePayWay::PAY_TYPE_BJ_CASH])) {
                                $store = '北京';
                            }
                        } else {
                            $store = '北京';
                        }
                    }

                    if (in_array($orderList[$orderNo]['pay_way'], PayConfig::_ALIPAY_PAY_WAYS_LIST)) {
                        $payWay = '支付宝';
                    } else if (in_array($orderList[$orderNo]['pay_way'], PayConfig::_WECHAT_PAY_WAYS_LIST)) {
                        $payWay = '微信';
                    } else if ($orderList[$orderNo]['pay_way'] == PayConfig::_PAY_WAYS_CUSTOMIZE_PAY_WAY) {
                        $payWay = '线下支付';
                        if (isset($customizePayList[$orderNo])) {
                            $payWay .= '-' . $customizePayList[$orderNo]['pay_way'];
                            $customizePayAmount = $customizePayList[$orderNo]['pay_amount'];
                            $customizeRemark = $customizePayList[$orderNo]['remark'];
                        }
                    }

                    foreach ($list as $skuId => $row) {
                        $goodsId = $row['goods_id'];
                        if ($allGoodsShippingAmount > 0) {
                            $shippingAmount = $row['shipping_amount'];
                        } else {
                            $shippingAmount = $orderList[$orderNo]['shipping_amount'];
                        }
                        $status = $orderList[$orderNo]['status_desc'];
                        $deliveryTime = strtotime($orderList[$orderNo]['delivery_time']) > 0 ? $orderList[$orderNo]['delivery_time'] : '';

                        if (isset($partOutOrderList[$orderNo])) {
                            if (isset($partOutOrderList[$orderNo][$skuId])) {
                                if ($orderList[$orderNo]['status'] == mOrder::STATUS_SEND_PART_OUT) {//部分发货
                                    $status = '待收货';
                                }
                                $deliveryTime = $partOutOrderList[$orderNo][$skuId]['delivery_time'];
                            } else {
                                if ($orderList[$orderNo]['status'] == mOrder::STATUS_SEND_PART_OUT) {//部分发货
                                    $status = '待发货';
                                }
                            }
                        }
                        $categoryStr = $categoryList[$goodsList[$goodsId]['category_id']] . '-' . $categoryList[$goodsList[$goodsId]['second_category_id']];
                        if ($goodsList[$goodsId]['third_category_id']) {
                            $categoryStr .= '-' . $categoryList[$goodsList[$goodsId]['third_category_id']];
                        }
                        $dataArr[$orderNo][$skuId] = [
                            //'num' => $i,
                            'order_no' => $orderNo,
                            'pay_no' => '\'' . $orderList[$orderNo]['pay_no'],
                            'pay_way' => $payWay,
                            'customize_pay_amount' => $customizePayAmount,
                            'customize_remark' => $customizeRemark,
                            'shop_id' => $row['shop_id'],
                            'goods_id' => $goodsId,
                            'sku_no' => '',
                            'title' => $row['title'],
                            'sku_desc' => $row['sku_desc'],
                            'category' => $categoryStr,
                            'order_no_p' => '',
                            'time' => $orderList[$orderNo]['pay_time'],//交易日期 支付时间
                            'promo_type' => $row['promo_type'] ? ActivityInfo::ACTIVITY_TYPES_MAPS[$row['promo_type']] : '',
                            'buy_num' => $row['buy_num'],
                            'buy_price' => $row['buy_price'],
                            'pay_amount' => $row['pay_amount'],
                            'shipping_amount' => $shippingAmount,
                            'commission_rate' => $row['commission_rate'],
                            'commissions' => strtotime($orderList[$orderNo]['confirm_time']) > 0 ? $row['commissions'] : '',
                            'settlement_amount' => strtotime($orderList[$orderNo]['confirm_time']) > 0 ? ($row['goods_amount'] + $shippingAmount - $row['commissions']) : '',
                            'delivery_time' => $deliveryTime,
                            'confirm_time' => strtotime($orderList[$orderNo]['confirm_time']) > 0 ? $orderList[$orderNo]['confirm_time'] : '',
                            'status' => $status,
                            'integral_amount' => $row['integral_amount'],
                            'coupon_amount' => isset($row['coupon_amount']) ? $row['coupon_amount'] : 0,
                            'activity_allowance' => isset($row['activity_allowance']) ? $row['activity_allowance'] : 0,
                            'packet_amount' => isset($row['packet_amount']) ? $row['packet_amount'] : 0,
                            'address' => $address,
                            'consignee_address' => $orderList[$orderNo]['consignee_address'],
                            'consignee_phone' => $orderList[$orderNo]['consignee_phone'],
                            'consignee_name' => $orderList[$orderNo]['consignee_name'],
                            'store' => $store,
                            'is_designer' => (isset($orderExtendList[$orderNo]['is_designer']) && $orderExtendList[$orderNo]['is_designer']) ? '是' : '否',
                            'brand_name' => $goodsList[$goodsId]['brand_name'],
                        ];
                        array_push($skuIds, (int)$skuId);
                        //$i++;
                    }
                    if (isset($orderRefundList[$orderNo])) {
                        foreach ($orderRefundList[$orderNo] as $orderRefundNo => $row) {
                            $refundIntegralAmount = $row['refund_integral_amount'];
                            $refundCouponAmount = isset($row['refund_coupon_amount']) ? $row['refund_coupon_amount'] : 0;
                            $refundActivityAllowance = isset($row['refund_activity_allowance']) ? $row['refund_activity_allowance'] : 0;
                            $refundPacketAmount = isset($row['refund_packet_amountt']) ? $row['refund_packet_amountt'] : 0;
                            $goodsId = $row['goods_id'];
                            $skuId = $row['sku_id'];
                            $categoryStr = $categoryList[$goodsList[$goodsId]['category_id']] . '-' . $categoryList[$goodsList[$goodsId]['second_category_id']];
                            if ($goodsList[$goodsId]['third_category_id']) {
                                $categoryStr .= '-' . $categoryList[$goodsList[$goodsId]['third_category_id']];
                            }
                            $dataArr[$row['refund_order_no']][$skuId] = [
                                //'num' => $i,
                                'refund_order_no' => $row['refund_order_no'],
                                'pay_no' => '',
                                'pay_way' => '',
                                'customize_pay_amount' => '',
                                'customize_remark' => '',
                                'shop_id' => $row['shop_id'],
                                'goods_id' => $row['goods_id'],
                                'sku_no' => '',
                                'title' => $row['title'],
                                'sku_desc' => $row['sku_desc'],
                                'category' => $categoryStr,
                                'order_no' => $orderNo,
                                'time' => $row['confirm_time'],//交易日期 退款日期
                                'promo_type' => '',
                                'buy_num' => '',
                                'buy_price' => '',
                                'refund_rmb' => 0 - $row['refund_rmb'],
                                'refund_shipping_amount' => 0 - $row['refund_shipping_amount'],
                                'commission_rate' => $orderGoodsList[$orderNo][$row['sku_id']]['commission_rate'],
                                'refund_commission_amount' => 0 - $row['refund_commission_amount'],
                                'settlement_amount' => 0 - ($row['refund_rmb'] + $row['refund_shipping_amount'] - $row['refund_commission_amount'] + $refundIntegralAmount + $refundCouponAmount + $refundActivityAllowance),
                                'delivery_time' => '',
                                'confirm_time' => '',
                                'status' => $orderList[$orderNo]['status_desc'],
                                'refund_integral_amount' => 0 - $refundIntegralAmount,
                                'refund_coupon_amount' => 0 - $refundCouponAmount,
                                'refund_activity_allowance' => 0 - $refundActivityAllowance,
                                'refund_packet_amount' => 0 - $refundPacketAmount,
                                'address' => $address,
                                'consignee_address' => $orderList[$orderNo]['consignee_address'],
                                'consignee_phone' => $orderList[$orderNo]['consignee_phone'],
                                'consignee_name' => $orderList[$orderNo]['consignee_name'],
                                'store' => $store,
                                'is_designer' => (isset($orderExtendList[$orderNo]['is_designer']) && $orderExtendList[$orderNo]['is_designer']) ? '是' : '否',
                                'brand_name' => $goodsList[$goodsId]['brand_name'],
                            ];
                            array_push($skuIds, (int)$skuId);
                            //$i++;
                        }
                    }
                }

                $skuList = GoodsSku::getInfoList($skuIds, false, false);
                $data = [];
                foreach ($dataArr as $orderNo => $orderList) {
                    foreach ($orderList as $skuId => $skuInfo) {
                        if (isset($skuList[$skuId])) {
                            $skuInfo['sku_no'] = $skuList[$skuId]['sku_no'];
                        }
                        $data[] = $skuInfo;
                    }
                }
                if ($data) {
                    $header = ['订单号', '支付宝/微信单号', '支付渠道（支付宝/微信）', '线下支付总金额', '备注', '商家uid', '产品代码', '商家编码', '产品名称', 'sku信息', '产品类别', '原订单号（适用退款）', '交易日期',
                        '活动', '数量', '商家单价', '交易金额', '运费', '合同佣金率%', '合同佣金金额', '商户扣除佣金后收款', '商家发货时间', '买家确认收货时间', '交易状态',
                        '积分', '优惠券', '活动补贴', '现金红包', '收货地所在区域', '收货地址', '收货人电话', '收货人姓名', '订单归属地', '是否是设计师', '品牌'
                    ];
                    $letter = range('A', 'Z');
                    array_push($letter, 'AA');
                    array_push($letter, 'AB');
                    array_push($letter, 'AC');
                    array_push($letter, 'AD');
                    array_push($letter, 'AE');
                    array_push($letter, 'AF');
                    array_push($letter, 'AG');
                    array_push($letter, 'AH');
                    array_push($letter, 'AI');
                    $excelObj = new DownExcel();
                    $excelObj->saveType = 1;
                    $excelObj->setFileName('商城交易台账');
                    $excelObj->setSheetTitle('商城交易台账');
                    //设置列宽
                    $widths = ['A' => 45, 'B' => 40, 'C' => 25, 'D' => 20, 'E' => 30, 'F' => 20, 'G' => 35, 'H' => 35, 'I' => 35,
                        'J' => 40, 'K' => 25, 'L' => 20, 'M' => 20, 'N' => 20, 'O' => 20, 'P' => 20, 'Q' => 20, 'R' => 20,
                        'S' => 25, 'T' => 25, 'U' => 25, 'V' => 20, 'W' => 20, 'X' => 20, 'Y' => 20, 'Z' => 20,
                        'AA' => 25, 'AB' => 25, 'AC' => 25, 'AD' => 40, 'AE' => 25, 'AF' => 30, 'AG' => 25, 'AH' => 25, 'AI' => 30
                    ];
                    $excelObj->setWidth($widths);
                    //默认单元格居中
                    $excelObj->setDefaultStyleAlignment();
                    $excelObj->freezePane('A1');//冻结窗口
                    $excelObj->setTextFormat('A');
                    $excelObj->setTextFormat('B');
                    $excelObj->setTextFormat('I');
                    $excelObj->setTextFormat('J');
                    $excelObj->setTextFormat('S');
                    $excelObj->setTextFormat('T');
                    foreach ($header as $key => $value) {
                        //设置字体
                        $excelObj->setFrontName($letter[$key]);
                        //设置加粗
                        $excelObj->setFrontBold("$letter[$key]1");
                        //设置大小
                        $excelObj->setFrontSize($letter[$key]);
                    }
                    $fileName = $excelObj->create($data, $header);
                    Mail::send($adminEmail, '商城交易台账' . $startTime . '-' . $endTime, '商城交易台账' . $startTime . '-' . $endTime, $fileName);
                }
            }
        }
    }

    /**
     * 交易明细下载
     * @param $shopId
     * @param $startTime
     * @param $endTime
     * @param $orderNo
     * @return bool
     */
    public static function admDownTransactionList($shopId, $startTime, $endTime, $orderNo)
    {
        $list = mSettlement::getTransactionList($shopId, $startTime, $endTime, $orderNo);
        if (!$list) {
            return false;
        }
        $data = [];
        foreach ($list as $rows) {
            $data[] = [
                'id' => $rows['id'],
                'shop_id' => $rows['shop_id'],
                'operate_time' => $rows['operate_time'],
                'transaction_type_desc' => $rows['transaction_type_desc'],
                'transaction_amount' => $rows['transaction_amount'],
                'order_no' => $rows['order_no'],
                'remark' => $rows['remark'],
            ];
        }

        $header = ['交易编号', '商家uid', '交易时间', '交易类型', '交易金额', '订单编号', '备注'];
        $letter = range('A', 'G');
        $excelObj = new DownExcel();
        $excelObj->setFileName('交易明细' . time());
        $excelObj->setSheetTitle('交易明细');
        //设置列宽
        $widths = ['A' => 18, 'B' => 25, 'C' => 30, 'D' => 20, 'E' => 40, 'F' => 45, 'G' => 60];
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        $excelObj->create($data, $header);
    }

    public static function monthList($start = 0, $end = 0)
    {
        $start = $start ? $start : strtotime('-12 month');
        $end = $end ? $end : time();
        if (!is_numeric($start) || !is_numeric($end) || ($end <= $start)) return [];
        $start = date('Y-m', $start);
        $end = date('Y-m', $end);
        $start = strtotime($start . '-01');
        $end = strtotime($end . '-01');
        $monthList = array();
        while ($start < $end) {
            $monthList[] = trim(date('Y-m', $start), ' ');
            $start += strtotime('+1 month', $start) - $start;
        }
        rsort($monthList);
        return $monthList;
    }
}